package mwa.cs.dao;
import java.io.IOException;
import java.util.List;
import java.util.Map;

import mwa.cs.database.Database;
import mwa.cs.model.MeterSize;

/**
 * A class used for managing MeterSize class
 */
public class MeterSizeDAO {
	
    /** a database of this meter_size */
    private Database db;

    /**
     * Instantiate a new object of this class
     * @param database a database
     */
    public MeterSizeDAO(Database db) {
        this.db = db;
    }

    /**
     * a method for searching all meter_size in database
     * @return all meter_size
     */
    public List<Map<String, Object>> findAll() {
        String vaSql = "SELECT * FROM meter_size";
        List<Map<String, Object>> result = db.queryList(vaSql);
        if (result != null) {
            return result;
        } else {
            return null;
        }
    }
    
    /**
     * a method for searching meter_size by siteId in database
     * @return all meter_size in siteId
     */
    
    public List<Map<String, Object>> findMeterSizeBySiteId(int pnSiteId) {
        String vaSql = "SELECT meter_size_code, description, fee FROM meter_size "
        		+ "WHERE site_id = ? AND request_type = '**' and receive_code = '**' "
        		+ "ORDER BY meter_size_code ASC";
        List<Map<String, Object>> result = db.queryList(vaSql, pnSiteId);
        if (result != null) {
            return result;
        } else {
            return null;
        }
    }
    
    //GET METER_SIZE 
    public List<Map<String, Object>> getAllMeterSizeBySiteId(int pnSiteId,String paMeterCode) {
        String vaSql = "SELECT MS.REQUEST_TYPE, QT.REQUEST_DESCRIPTION , MS.RECEIVE_CODE ,RC.DESCRIPTION , MS.FEE FROM METER_SIZE MS "
        		+ "INNER JOIN REQUEST_TYPE QT ON MS.REQUEST_TYPE = QT.REQUEST_TYPE "
        		+ "INNER JOIN RECEIVE_CODE RC ON MS.RECEIVE_CODE = RC.RECEIVE_CODE "
        		+ " WHERE MS.SITE_ID = ? AND MS.METER_SIZE_CODE = ? AND MS.REQUEST_TYPE != '**'"
        		+ " ORDER BY MS.REQUEST_TYPE, MS.RECEIVE_CODE";
        List<Map<String, Object>> result = db.queryList(vaSql, pnSiteId,paMeterCode);
        if (result != null){
        	 return result;
        }else {
            return null;
        }
    }
   
    //check  repeat request_type 
    public String checkRequestType(int pnSiteId,String paRequestType, String paMeterCode) {
        String vaSql = "SELECT request_type FROM meter_size WHERE site_id = ? and request_type = ? and meter_size_code = ? ";
        Map<String, Object> result = db.querySingle(vaSql, pnSiteId, paRequestType, paMeterCode);
        if (result != null) {
            return (String) result.get("REQUEST_TYPE").toString();
        } else {
            return null;
        }
    }
    
    public String findDescriptionBySiteAndCode(int pnSiteId, String paMeterSizeCode) {
        String vaSql = "SELECT description FROM meter_size WHERE site_id = ? and meter_size_code = ? "
        		+ "and request_type = '**' and receive_code = '**'";
        Map<String, Object> result = db.querySingle(vaSql, pnSiteId, paMeterSizeCode);
        if (result != null) {
           
        	return (String) result.get("DESCRIPTION");
        } else {
            return null;
        }
    }
    
    public String findDescriptionAndFeeBySiteAndCode(int pnSiteId, String paMeterSizeCode,String pafield) {
        String vaSql = "SELECT "+pafield +" FROM meter_size WHERE site_id = ? and meter_size_code = ?"
        		+ "and request_type = '**' and receive_code = '**'";
        Map<String, Object> result = db.querySingle(vaSql, pnSiteId, paMeterSizeCode);
        if (result != null) {
           
        	return (String) result.get(pafield).toString();
        	
        } else {
            return null;
        }
    }

    //get meter_size_code and check for insert data
    public String findMeterSizeCodeBySiteAndMeterSizeCode(int pnSiteId, String pnMeterSize) {
        String vaSql = "SELECT meter_size_code FROM meter_size WHERE site_id = ? and meter_size_code = ? ";
        Map<String, Object> result = db.querySingle(vaSql, pnSiteId, pnMeterSize);
        if (result != null) {
            return (String) result.get("METER_SIZE_CODE").toString();
        } else {
            return null;
        }
    }
    
  //get meter_size_code for check duplicate for update data
    public String findsMeterSizeCodeBySiteAndMeterSizeCode(int pnSiteId, String paOldMeterSize,String paNewMeterSize) {
        String vaSql = "SELECT meter_size_code FROM meter_size WHERE site_id = ? and meter_size_code != ? and meter_size_code=? ";
        Map<String, Object> result = db.querySingle(vaSql, pnSiteId, paOldMeterSize,paNewMeterSize);
        if (result != null) {
            return (String) result.get("METER_SIZE_CODE");
        } else {
            return null;
        } 
    }
    
    
    //get receive_code and fee for edit 
    public List<Map<String, Object>> getReceiveCodeAndFee(int pnSiteId,String paRequestType,String paMeterCode) {
  		String vaSql = "SELECT RECEIVE_CODE , FEE FROM METER_SIZE WHERE SITE_ID = ? "
  				+ "AND REQUEST_TYPE !='**'"
  				+ "AND REQUEST_TYPE =? "
  				+ "AND METER_SIZE_CODE = ? ";
  		List<Map<String, Object>> result = db.queryList(vaSql, pnSiteId,paRequestType,paMeterCode);
  		return result;
  	}
    
    //get receive_code for check in database 
    public String getReceiveCode(int pnSiteId,String paRequestType ,String paReceiveCode,String paMeterCode ) {
  		String vaSql = "SELECT RECEIVE_CODE FROM METER_SIZE WHERE  SITE_ID = ? "
  				+ "AND REQUEST_TYPE !='**' "
  				+ "AND REQUEST_TYPE =? "
  				+ "AND RECEIVE_CODE =? "
  				+ "AND METER_SIZE_CODE =? ";
  		 Map<String, Object> result = db.querySingle(vaSql, pnSiteId, paRequestType,paReceiveCode,paMeterCode);
         if (result != null) {
             return (String) result.get("RECEIVE_CODE").toString();
         } else {
             return null;
         }
  	}
    
    /**
     * a method for adding a meter_size into database
     * @param poZone METER_SIZE to add
     */
    public void addMeterSize(MeterSize poMeterSize) {
   	String vaSql = "Insert into METER_SIZE (SITE_ID,METER_SIZE_CODE,REQUEST_TYPE,RECEIVE_CODE,"
   			     + "DESCRIPTION,FEE,INSERT_USER_ID,INSERT_DATE,INSERT_TIME,UPDATE_USER_ID,UPDATE_DATE,UPDATE_TIME)"
   			    + "values (?,?,?,?,?,?,?,?,?,?,?,?) ";
   	 
   	 
   			try {
   					db.insert(vaSql, poMeterSize.getVnSiteId(),
   								poMeterSize.getVaMeterSizeCode(),
   								poMeterSize.getVaRequestType(),
   								poMeterSize.getVaReceiveCode(),
   								poMeterSize.getVaDescription(),
   								poMeterSize.getVnFee(),
   								poMeterSize.getVnInsertUserId(),
   								poMeterSize.getVaInsertDate(),
   								poMeterSize.getVaInsertTime(),
   								poMeterSize.getVnUpdateUserId(),
   								poMeterSize.getVaUpdateDate(),
   								poMeterSize.getVaUpdateTime()
   								
   								);
   							
   			}catch (Exception e) {
   	        	throw new RuntimeException(e);
   	        }
   	}
    
    /**
     * a method for updating a mater_size into database
     * @param pnSiteId site that meter_size live
     * @param pnCodeId that will update
     * @param poNewDescription that will update
     * @param poNewFee that will update
     */
    public void updateMeterSize(int pnSiteId,String pnCodeId, MeterSize poNewMeterSize) {
    	String vaSql  = "UPDATE meter_size SET " +
				  " METER_SIZE_CODE = ? , " +
				  " DESCRIPTION = ? , " +
				  " FEE = ? ," +
				  " UPDATE_USER_ID = ? , "+
				  " UPDATE_DATE = ? , " +
				  " UPDATE_TIME = ? " +
				  " WHERE " + 
				  " METER_SIZE_CODE = ? " +
				  " AND SITE_ID = ?";
	
	 try {
			db.update(vaSql,poNewMeterSize.getVaMeterSizeCode(),
						poNewMeterSize.getVaDescription(),
						poNewMeterSize.getVnFee(),
						poNewMeterSize.getVnUpdateUserId(),
						poNewMeterSize.getVaUpdateDate(),
						poNewMeterSize.getVaUpdateTime(),
						pnCodeId,
						pnSiteId);
						
			
	}catch (Exception e) {
		throw new RuntimeException(e);
	}
    	
    }
    
    //update Meter_size from CS11007
    public void updateMeteSizeByRequestType(int pnSiteId,String paMeterCode,String paRequestType,String paReceiveCode,MeterSize poNewMeterSize) {
    	String vaSql  = "UPDATE METER_SIZE SET  METER_SIZE_CODE = ?,"
    					+ "REQUEST_TYPE = ?,"
    					+ "RECEIVE_CODE = ?,"
		    			+ "DESCRIPTION =?, "
		    			+ "FEE = ?,"
		    			+ "UPDATE_USER_ID = ?, "
		    			+ "UPDATE_DATE = ?,"
		    			+ "UPDATE_TIME = ? "
		    			+ "WHERE "
		    			+ "SITE_ID = ?"
		    			+ " AND METER_SIZE_CODE =?"
		    			+ " AND REQUEST_TYPE =?"
		    			+ " AND RECEIVE_CODE =? ";
	 try {
			db.update(vaSql,poNewMeterSize.getVaMeterSizeCode(),
						poNewMeterSize.getVaRequestType(),
						poNewMeterSize.getVaReceiveCode(),
						poNewMeterSize.getVaDescription(),
						poNewMeterSize.getVnFee(),
						poNewMeterSize.getVnUpdateUserId(),
						poNewMeterSize.getVaUpdateDate(),
						poNewMeterSize.getVaUpdateTime(),
						pnSiteId,
						paMeterCode,
						paRequestType,
						paReceiveCode);
	}catch (Exception e) {
		throw new RuntimeException(e);
	}
    }
    
    /**
     * delete meter_size by site_id, meter_size_code
     * @param pnSiteId is an site_id that want to delete data
     * @param pnMeterSizeCode is an pnId that want to delete data
     * @return true if success, false if fail
     */
    public boolean deleteMeterSizeByMeterSizeCode(int pnSiteId, String pnMeterSizeCodeId) {
        String vaSql = "DELETE FROM meter_size WHERE site_id = ? and meter_size_code = ?";
        try {
            db.update(vaSql, pnSiteId, pnMeterSizeCodeId);
            return true;
        } catch (Exception e) {
            return false;
        }
    }
    
    
    public List<Map<String, Object>> findDescriptionInReceiveCodeByRequestTypeAndMeterSize(int pnSiteId, String paRequestTypeString, String paMeterSize) {
    	String vaSql = "SELECT mt.receive_code , rc.description, mt.fee FROM meter_size mt JOIN receive_code rc ON mt.receive_code = rc.receive_code "
    			+ " WHERE mt.site_id = ? AND mt.request_type = ? AND mt.meter_size_code =  ? ORDER BY mt.receive_code";
    	List<Map<String, Object>> result = db.queryList(vaSql, pnSiteId, paRequestTypeString,paMeterSize);
        if (result != null) {
            return result;
        } else {
            return null;
        }
    }
    
    public boolean deleteReceiveCodeANDFee(int pnSiteId,String paRequestType,String paMeterCode ){
    	  
        String vaSql = "DELETE FROM METER_SIZE WHERE  SITE_ID = ? "
    				+ "AND REQUEST_TYPE !='**' "
    				+ "AND REQUEST_TYPE =? "
    				+ "AND METER_SIZE_CODE =? ";
        try {
            db.update(vaSql, pnSiteId,paRequestType,paMeterCode);
            return true;
        } catch (Exception e) {
            return false;
        }
    }
  
    /**
     * @athor CEO FreeALife
     * this is get value for calculate money register user PAPA
     * @param pnSiteId
     * @param requestType
     * @param meterCode
     * @return
     * @throws IOException
     */
    public List<Map<String, Object>> getValueCalcuate(int pnSiteId ,int pnMeterCode)  throws IOException {
  		String vaSql = " SELECT RC.RECEIVE_CODE , RC.DESCRIPTION  , MS.FEE  FROM RECEIVE_CODE RC " +
  					 " INNER JOIN METER_SIZE  MS  ON RC.RECEIVE_CODE  = MS.RECEIVE_CODE AND RC.SITE_ID = MS.SITE_ID "  + 
					 " WHERE MS.SITE_ID =  ? AND  " +
					 " MS.METER_SIZE_CODE  = ?  AND  " +
					 " MS.REQUEST_TYPE = '01' ";
  		List<Map<String, Object>> result = db.queryList(vaSql, pnSiteId,pnMeterCode);
  		return result;
  	}
    
}
